﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Web;
using System.Web.Security;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Library.Helpers;
using Eacademy.Library.Membership;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;
using SubSonic.DataProviders;

namespace Eacademy.Domain.Repository
{
    public class StudentRepository : IStudentRepository
    {
        EacademyDB db = new EacademyDB();

        private static CustomMembershipProvider Provider
        {
            get
            {
                return (CustomMembershipProvider)Membership.Provider;
            }
        }

        public AdminStudentsViewModel GetStudentByStudentId(int id)
        {
            AdminStudentsViewModel studentsViewModel = new AdminStudentsViewModel();
            try
            {
                Student student = db.Select.From<Student>().Where(StudentsTable.StudentIdColumn).IsEqualTo(id).ExecuteSingle<Student>();
                User user = db.Select.From<User>().Where(UsersTable.UserIdColumn).IsEqualTo(student.UserId).ExecuteSingle<User>();

                studentsViewModel.StudentNumber = student.StudentNumber;
                studentsViewModel.Username = user.Username;
                studentsViewModel.FirstName = student.Firstname;
                studentsViewModel.MiddleName = student.Middlename;
                studentsViewModel.LastName = student.Lastname;
                studentsViewModel.Password = user.Password;
                studentsViewModel.PrimaryEmail = user.UserPrimaryEmail;
                studentsViewModel.SecondaryEmail = user.UserAlternateEmail;
                studentsViewModel.StudentId = student.StudentId.ToInteger();
                studentsViewModel.ThumbImageName = user.ThumbImageName;

                return studentsViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return null;
        }


        public AdminStudentsViewModel GetStudentByUserId(int id)
        {
            AdminStudentsViewModel studentsViewModel = new AdminStudentsViewModel();
            try
            {

                User user = db.Select.From<User>().Where(UsersTable.UserIdColumn).IsEqualTo(id).ExecuteSingle<User>();
                Student student = db.Select.From<Student>().Where(StudentsTable.UserIdColumn).IsEqualTo(user.UserId).ExecuteSingle<Student>();

                studentsViewModel.StudentNumber = student.StudentNumber;
                studentsViewModel.Username = user.Username;
                studentsViewModel.FirstName = student.Firstname;
                studentsViewModel.MiddleName = student.Middlename;
                studentsViewModel.LastName = student.Lastname;
                studentsViewModel.Password = user.Password;
                studentsViewModel.PrimaryEmail = user.UserPrimaryEmail;
                studentsViewModel.SecondaryEmail = user.UserAlternateEmail;
                studentsViewModel.StudentId = student.StudentId.ToInteger();
                studentsViewModel.ThumbImageName = user.ThumbImageName;

                return studentsViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return null;
        }

        public User AddStudent(AdminStudentsViewModel adminStudentsViewModel)
        {
            try
            {
                User user = new User();
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        //User
                        user.UserPrimaryEmail = adminStudentsViewModel.PrimaryEmail;
                        user.UserAlternateEmail = adminStudentsViewModel.SecondaryEmail;
                        user.Username = adminStudentsViewModel.Username;
                        user.PasswordSalt = KeyGenerator.GenerateRandomKey(15, 15, true, true, true);
                        user.Password = PasswordHelper.ComputeHash(adminStudentsViewModel.Password, user.PasswordSalt);
                        //user.Password = adminUsersViewModel.Password;
                        user.LastUserActivity = DateTime.Now;
                        user.ThumbImageName = adminStudentsViewModel.ThumbImageName;

                        Student student = new Student();
                        user.UserTypeId = UserTypes.Student;
                        user.Save();
                        student.UserId = user.UserId;
                        student.StudentNumber = adminStudentsViewModel.StudentNumber;
                        student.Firstname = adminStudentsViewModel.FirstName;
                        student.Middlename = adminStudentsViewModel.MiddleName;
                        student.Lastname = adminStudentsViewModel.LastName;
                        student.Save();

                    }
                    ts.Complete();
                }

                return user;
            }
            catch (Exception ex)
            { }
            return null;
        }

        public User UpdateStudent(AdminStudentsViewModel studentsViewModel)
        {
            try
            {
                User user = new User();
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Student student = db.Select
                            .From<Student>()
                            .Where(StudentsTable.StudentIdColumn)
                            .IsEqualTo(studentsViewModel.StudentId)
                            .ExecuteSingle<Student>();

                        user = db.Select
                            .From<User>()
                            .Where(UsersTable.UserIdColumn).IsEqualTo(student.UserId)
                            .ExecuteSingle<User>();

                        student.StudentNumber = studentsViewModel.StudentNumber;
                        student.Firstname = studentsViewModel.FirstName;
                        student.Middlename = studentsViewModel.MiddleName;
                        student.Lastname = studentsViewModel.LastName;

                        student.Update();

                        user.UserAlternateEmail = studentsViewModel.SecondaryEmail;
                        user.UserPrimaryEmail = studentsViewModel.PrimaryEmail;
                        user.ThumbImageName = studentsViewModel.ThumbImageName;

                        user.Update();

                    }
                    ts.Complete();
                }
                return user;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return null;
        }

        public List<StudentCompactViewModel> GetStudents(int pageIndex, int pageSize, StudentFilterViewModel parameters, out int totalCount, bool count = true)
        {

            StudentFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<StudentCompactViewModel> sudentViewList = new List<StudentCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT *  ");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM students s ");

                sql.Append(" WHERE 1 = 1 ");

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                sudentViewList = studentsTable.AsEnumerable().Select(datarow => new StudentCompactViewModel
                {
                    FirstName = datarow["firstname"].ToString(),
                    MiddleName = datarow["middlename"].ToString(),
                    LastName = datarow["lastname"].ToString(),
                    StudentNumber = datarow["student_number"].ToString(),
                    StudentId = datarow["student_id"].ToInteger()
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return sudentViewList;
            }
            catch (Exception ex)
            { }
            return sudentViewList;
        }

        public List<StudentCompactViewModel> GetStudentsBySubjectAndUserId(int pageIndex, int pageSize, int subjectId, int userId, bool status, StudentFilterViewModel parameters, out int totalCount, bool count = true)
        {

            StudentFilterViewModel searchparameter = parameters;
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<StudentCompactViewModel> sudentViewList = new List<StudentCompactViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT s.firstname,");
                sql.AppendLine(@"       s.middlename,");
                sql.AppendLine(@"       s.lastname,");
                sql.AppendLine(@"       s.student_number,");
                sql.AppendLine(@"       s.student_id,");
                sql.AppendLine(@"       s.user_id as student_user_id,");
                sql.AppendLine(@"       m.user_id");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM   subjects_enrolled se");
                sql.AppendLine(@"       LEFT JOIN students s");
                sql.AppendLine(@"         ON s.user_id = se.user_id");
                sql.AppendLine(@"       LEFT JOIN mentors m");
                sql.AppendLine(@"         ON se.mentor_id = m.mentor_id");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND  m.user_id = ?UserId");
                sql.AppendLine(@"       AND se.subject_id = ?SubjectId");
                sql.AppendLine(@"       AND enabled = ?Enabled  ");

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?UserId", DbType.Int32, userId);
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectId);
                db.AddInParameter(comm, "?Enabled", DbType.Boolean, status);

                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                sudentViewList = studentsTable.AsEnumerable().Select(datarow => new StudentCompactViewModel
                {
                    FirstName = datarow["firstname"].ToString(),
                    MiddleName = datarow["middlename"].ToString(),
                    LastName = datarow["lastname"].ToString(),
                    StudentNumber = datarow["student_number"].ToString(),
                    StudentId = datarow["student_id"].ToInteger(),
                    UserId = datarow["student_user_id"].ToInteger()
                }).ToList();


                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }

                return sudentViewList;
            }
            catch (Exception ex)
            { }
            return sudentViewList;
        }


        public bool DeleteStudent(int id)
        {
            try
            {
                StudentBase.Delete(x => x.StudentId == id);
                return true;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return false;
        }

        public Boolean AcceptStudent(int id, int subjectid, int mentorUserId)
        {
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            try
            {
                StringBuilder sql = new StringBuilder(142);
                sql.AppendLine(@"UPDATE subjects_enrolled se");
                sql.AppendLine(@"SET    enabled = 1");
                sql.AppendLine(@"WHERE  1 = 1");
                sql.AppendLine(@"       AND user_id = ?UserId");
                sql.AppendLine(@"       AND subject_id = ?SubjectId");
                sql.AppendLine(@"       AND mentor_id = (SELECT m.mentor_id FROM mentors m WHERE m.user_id = ?MentorUserId);  ");

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?UserId", DbType.Int32, id);
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectid);
                db.AddInParameter(comm, "?MentorUserId", DbType.Int32,mentorUserId);

                factory.ExecuteNonQuery(comm);

                return true;
            }
            catch (Exception ex)
            { }
            return false;
        }

        #region IStudentRepository Members


        public AdminStudentsViewModel EditStudent(int id)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}